﻿using CloudDcPc.Entity;
using CloudDcPc.util;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;

namespace CloudDcPc.Bean
{
    public class UserBean
    {
        private MySqlConnection myConn = null;
        private DBConnection dbc = null;
        private MySqlCommand cmd = null;
        public UserBean()
        {
            this.dbc = new DBConnection();
            this.myConn = this.dbc.getConnection();
        }

        public bool UpdateUserPwd(User user,string pwd)
        {
            string sql = "update dc_user set pwd=md5('"+pwd+"') where id='" + user.Id + "'";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed)System.Threading.Thread.Sleep(1000);myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                cmd.ExecuteNonQuery();
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                dbc.close();
            }
        }

        public bool DelUserById(int id)
        {
            string sql = "delete from dc_user where id='"+id+"'";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed)System.Threading.Thread.Sleep(1000);myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                cmd.ExecuteNonQuery();
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                dbc.close();
            }
        }


        public bool AddUser(User user)
        {
            string sql = "insert into dc_user(user_num,uname,pwd,dep,type) values('" + user.User_num + "','" + user.Uname + "','" + StringUtil.GetMD5(user.User_num) + "','" + user.Dep + "','" + user.Type + "')";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed)System.Threading.Thread.Sleep(1000);myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                cmd.ExecuteNonQuery();
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                dbc.close();
            }
        }

        public bool ResetPwdById(int id)
        {
            string sql = "update dc_user set pwd=md5(user_num) where id='" + id + "'";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed)System.Threading.Thread.Sleep(1000);myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                cmd.ExecuteNonQuery();
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                dbc.close();
            }
        }


        public bool UpdateUser(User user)
        {
            string sql = "update dc_user set uname='" + user.Uname + "',dep='" + user.Dep + "',type='" + user.Type + "' where id='" + user.Id + "'";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed)System.Threading.Thread.Sleep(1000);myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                cmd.ExecuteNonQuery();
                return true;
            }catch{
                return false;
            }finally{
                dbc.close();
            }
            
        }


        public ObservableCollection<User> GetAllUser()
        {
            ObservableCollection<User> list = new ObservableCollection<User>();
            User user = null;
            string sql = "select * from dc_user order by type";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    user = new User((int)reader["id"], (string)reader["user_num"], (string)reader["uname"], (string)reader["pwd"], (string)reader["dep"], (int)reader["type"]);
                    list.Add(user);
                }

            }
            finally
            {
                dbc.close();
            }
            return list;
        }

        public ObservableCollection<User> GetAllUserNotZJL()
        {
            ObservableCollection<User> list = new ObservableCollection<User>();
            User user = null;
            string sql = "select * from dc_user where type!=4 order by type";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    user = new User((int)reader["id"], (string)reader["user_num"], (string)reader["uname"], (string)reader["pwd"], (string)reader["dep"], (int)reader["type"]);
                    list.Add(user);
                }

            }
            finally
            {
                dbc.close();
            }
            return list;
        }


        /// <summary>
        /// 验证用户
        /// </summary>
        /// <param name="user_num">用户名</param>
        /// <param name="pwd">密码（未加密的）</param>
        /// <returns>1：成功 0：失败 -1：发生异常</returns>
        public int VerifyUser(string user_num, string pwd)
        {
            string sql = "select * from dc_user where user_num=@user_num and pwd=MD5('" + pwd + "')";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed)System.Threading.Thread.Sleep(1000);myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                cmd.Parameters.Add(new MySqlParameter("user_num", user_num));
                cmd.Parameters.Add(new MySqlParameter("pwd", StringUtil.GetMD5(pwd)));
                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    if ((int)reader["type"] == 1)
                        return -2;
                    return 1;
                }
                    
                return 0;
            }
            catch
            {
                return -1;
            }
            finally
            {
                dbc.close();
            }
        }

        public List<User> GetUsersByField(string field, string which)
        {
            List<User> list = new List<User>();
            User user = null;
            string sql = "select * from dc_user where " + field + "=@which";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed)System.Threading.Thread.Sleep(1000);myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                cmd.Parameters.Add(new MySqlParameter("which", which));
                MySqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    user = new User((int)reader["id"], (string)reader["user_num"], (string)reader["uname"], (string)reader["pwd"], (string)reader["dep"], (int)reader["type"]);
                    list.Add(user);
                }

            }
            finally
            {
                dbc.close();
            }
            return list;
        }


        public User GetUserByField(string field,string which){
            User user = null;
            string sql = "select * from dc_user where " + field + "=@which";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed)System.Threading.Thread.Sleep(1000);myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                cmd.Parameters.Add(new MySqlParameter("which", which));
                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    user = new User((int)reader["id"], (string)reader["user_num"], (string)reader["uname"], (string)reader["pwd"], (string)reader["dep"], (int)reader["type"]);
                }
                
            }
            finally
            {
                dbc.close();
            }
            return user;
        }
    }
}
